import xlrd, xlsxwriter
import os, sys, re, logging
from pprint import pprint
 
print('EXCEL 合并工具 V11   --by microholmes 2021.1')
 
logging.basicConfig(filename=sys.argv[0] + '.log', level=logging.INFO,
                    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
 
dir = sys.argv[1]
# dir = r'C:\Users\Eyes\Desktop\已统计（请勿上传）'
r = re.search('(.+)[\\\]', dir)
rr = re.search('[\\\]([^\\\]+)$', dir)
parentDir = r.group(1)
lastDir = rr.group(1)
 
resultFile = parentDir + '\\汇总：' + lastDir + '.xlsx'
new_wb = xlsxwriter.Workbook(resultFile)  # 创建excel文件
new_ws = new_wb.add_worksheet('统计')  # 括号内为工作表表名
 
filenames = os.listdir(dir)
isFirstFile = True
newWsRowNum = 0
 
# 错误的文件格式
validTypeFiles = []
# 统计标题相同的文件数，以判断文件结构是否被修改
sameTitleFiles = {}
 
# 保存统计第几个表的索引
summarySheetIndex = None
# 标题所在行
titleRowNum = None
 
# 开始文件循环
for filename in filenames:
    if not re.search(r'\.xlsx?$', filename, re.I):
        validTypeFiles.append(filename)
        continue
    logger.info('开始处理：' + filename)
    file = dir + '/' + filename
    wb = xlrd.open_workbook(filename=file)
    __n__ = 0
    # 统计第几个表
    if summarySheetIndex == None:
        print('\n在工作薄中发现以下工作表：')
        for sheetName in wb.sheet_names():
            print(__n__, sheetName)
            __n__ += 1
    
    # 确定标题所在行
    if titleRowNum==None:
        n = input('\n请输入标题所在行号[2]：').strip()
        if n != '':
            titleRowNum = int(n)
        else:
            titleRowNum=2

    for sheetIndex in range(0, __n__):
        print('拼接sheet' + str(sheetIndex))
        ws = wb.sheet_by_index(sheetIndex)

        # 保存第一列标题名称至sameTitleFiles变量
        firstTitle = ws.cell(titleRowNum - 1, 0).value
        if firstTitle not in sameTitleFiles:
            sameTitleFiles[firstTitle] = []
        sameTitleFiles[firstTitle].append(filename)

        # 第一行写入标题
        if isFirstFile:
            # 写入文件名
            new_ws.write(newWsRowNum, ws.ncols, '文件名')
            # 逐个写入标题
            for y in range(0, ws.ncols):
                new_ws.write(newWsRowNum, y, ws.cell(titleRowNum - 1, y).value)
            isFirstFile = False
            newWsRowNum += 1
        # 写入数据
        for x in range(titleRowNum, ws.nrows):
            for y in range(0, ws.ncols):
                new_ws.write(newWsRowNum, y, ws.cell(x, y).value)
            # 写入文件名
            new_ws.write(newWsRowNum, ws.ncols, filename + '-sheet' + str(sheetIndex))

            newWsRowNum += 1
    # 打印文件名
    print(filename)
 
new_wb.close()
 
print('\n汇总完成，文件地址  ', resultFile)
 
if validTypeFiles:
    print('\n错误格式的文件：', validTypeFiles)
 
# 计算出第一列标题内容相同的最大数量
count = {}
for k, v in sameTitleFiles.items():
    count[k] = len(v)
maxLen = max(count.values())
 
# 保存可能有错误的文件名
maybeErrors = []
# 挑选出与字段最大相同数不同的文件
for k, v in sameTitleFiles.items():
    if len(v) < maxLen:
        maybeErrors += v
 
if len(maybeErrors) > 0:
    print('\n\n以下文件结构被改变，有可能造成统计错误，请仔细查看：\n')
    for i in maybeErrors:
        print(i)
 
input('\n完成 按任意键退出')